﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using DCTLib.Common;

public partial class AdminZone_CalculatePoints : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string confirm = Request.QueryString["confirm"].ToString();
            int point = 0;
            try
            {
                point = int.Parse(Request.QueryString["point"]);
            }
            catch { point = 0; }
            int productId;
            try
            {
                productId = int.Parse(Request.QueryString["productId"]);
            }
            catch { productId = 0; }

            string user = string.Empty;
            try
            {
                user = Request.QueryString["id"];
            }
            catch (System.Exception ex)
            {
                user = "";
            }

            if (Request.QueryString["confirm"] != null && !string.IsNullOrEmpty(Request.QueryString["confirm"]))
            {
                if (confirm == "false" && point > 0 && productId > 0)
                {
                    string msg = string.Format(@"<h3>Are you sure ordering for {0} points? If ok</h3>, 
                        <a href='CalculatePoints.aspx?id={2}&productId={1}&point={0}&confirm=true'><h2>Click here</h2></a>", point, productId, user);
                    msg += "<br><a href=\"TestingSystem.aspx\"><h4><< Click here go back </h4></a>";
                    lblAlert.Text = msg;
                }
                else
                {
                    try
                    {
                        string username = user;
                        point = int.Parse(Request.QueryString["point"]);
                        productId = int.Parse(Request.QueryString["productId"]);

                        //Khi click vào Banner thì gọi tới hàm này để xử lý tính toán điểm cho các cấp.
                        ExecCalculatePoint(username, point, productId);
                    }
                    catch
                    {
                        lblAlert.Text = "<font color='red'><b>[Error]</b></font>";
                        lblAlert.Text += "<br><a href=\"TestingSystem.aspx\"><< Click here go back </a>";
                    }
                }
            }
        }
    }

    protected void ExecCalculatePoint(string username, decimal point, int productId)
    {
        //Update lại số điểm mà username này order
        string sql = string.Format(@"            
            DECLARE @memID int
            SET @memID = (SELECT MemberID FROM Web_Members WHERE username='{0}')
            DECLARE @currentPoint decimal(18,2)
            SET @currentPoint = 
            (
	            SELECT PointNumber FROM [Property] WHERE 
	            MemberID=(SELECT MemberID FROM Web_Members WHERE username='{0}')
            )
            IF(@currentPoint-{1}>=0)
            BEGIN 
                DECLARE @LastMoney decimal(20,2)
                SET @LastMoney = (SELECT (PointNumber-{1}) FROM [Property] WHERE MemberID=@memID)
                SET XACT_ABORT ON  
	            BEGIN TRAN  
	            BEGIN TRY 
                    UPDATE [Property]
                    SET PointNumber = @LastMoney
                    WHERE MemberID=@memID
                    
                    INSERT INTO OrderDetails(MemberID,ProductID,UnitPrice)
		            VALUES(@memID,{2},{1})

                    INSERT INTO TransactionLogs(MemberID,FirstMoney,LastMoney,ReasonChange)
		            VALUES(@memID,@currentPoint,@LastMoney,N'Giao dịch {1} points')

                    COMMIT
	                SELECT 'true'
                END TRY 
                BEGIN CATCH  
                   ROLLBACK  
                   SELECT 'false'
                END CATCH
            END 
            ELSE SELECT 'false'", username, point, productId);
        bool isValid = bool.Parse(SQLHelper.TExecuteScalar(sql).ToString());


        //Lấy ra UniqueCode của User hiện tại mua hàng.
        sql = string.Format("select uniquecode FROM Web_Members where username='{0}'", username);
        string uniquecode = SQLHelper.TExecuteScalar(sql).ToString();
        string[] arrUserLevels = uniquecode.Split('|');
        List<string> listUserIDLevels = new List<string>();
        if (arrUserLevels.Length > 1)
        {
            for (int i = 0; i < arrUserLevels.Length - 1; i++)
            {
                listUserIDLevels.Add(arrUserLevels[i]);
            }
        }

        List<string> ListMemID = new List<string>();
        //Lấy những user cùng level với userLogin thỏa mãn RegDate < RegDate của userLogin
        sql = string.Format(@"SELECT MemberID FROM Web_Members 
            WHERE levelinfo=(SELECT LevelInfo FROM Web_Members WHERE UserName='{0}')
            AND RegDate < (SELECT RegDate FROM Web_Members WHERE UserName='{0}')
            AND SUBSTRING(REVERSE(UniqueCode), charindex('|',REVERSE(UniqueCode))+1,1000) = 
            (SELECT SUBSTRING(REVERSE(UniqueCode), charindex('|',REVERSE(UniqueCode))+1,1000) 
            FROM Web_Members WHERE UserName='{0}')
            ORDER BY RegDate desc", username);
        DataTable tbtmp = SQLHelper.GetAll(sql);
        if (tbtmp != null && tbtmp.Rows.Count > 0)
        {
            foreach (DataRow row in tbtmp.Rows)
                ListMemID.Add(row["MemberID"].ToString());
        }

        //Lấy những user cấp trên của userLogin
        if (listUserIDLevels.Count > 0)
        {
            for (int i = 0; i < listUserIDLevels.Count; i++)
            {
                ListMemID.Add(listUserIDLevels[i]);
                sql = string.Format(@"SELECT MemberID FROM Web_Members 
                    WHERE levelinfo=(SELECT LevelInfo FROM Web_Members WHERE MemberID='{0}')
                    AND RegDate < (SELECT RegDate FROM Web_Members WHERE MemberID='{0}')
                    AND SUBSTRING(REVERSE(UniqueCode), charindex('|',REVERSE(UniqueCode))+1,1000) = 
                    (SELECT SUBSTRING(REVERSE(UniqueCode), charindex('|',REVERSE(UniqueCode))+1,1000) 
                    FROM Web_Members WHERE MemberID='{0}')
                    ORDER BY RegDate desc", listUserIDLevels[i]);
                tbtmp = SQLHelper.GetAll(sql);
                if (tbtmp != null && tbtmp.Rows.Count > 0)
                {
                    foreach (DataRow row in tbtmp.Rows)
                        ListMemID.Add(row["MemberID"].ToString());
                }
            }
        }

        string sqlTransBeginTry = @"
                SET XACT_ABORT ON  
	            BEGIN TRAN  
	            BEGIN TRY ";
        string sqlTransEndTry = @"
                    SELECT 'true'
		            COMMIT
                END TRY ";
        string sqlTransCatch = @"
                BEGIN CATCH  
                   ROLLBACK  
                   SELECT 'false'
                END CATCH";
        string sqlQuery = string.Empty;
        if (ListMemID != null && ListMemID.Count > 0)
        {
            if (isValid)
            {
                sqlQuery += @"
                DECLARE @FirstMoney decimal(18,2)
                DECLARE @LastMoney decimal(20,2)";
                //Lấy thông tin bảng Config
                sql = string.Format(@"SELECT * FROM Configs ORDER BY NumOfPeople");
                tbtmp = SQLHelper.GetAll(sql);
                if (tbtmp != null && tbtmp.Rows.Count > 0)
                {
                    for (int i = 0; i < tbtmp.Rows.Count; i++)
                    {
                        int iNumOfPeople = int.Parse(tbtmp.Rows[i]["NumOfPeople"].ToString());
                        if (ListMemID.Count <= iNumOfPeople)
                        {
                            //Đếm xem có bao nhiêu cấp trong bảng Configs
                            sql = string.Format(@"SELECT * FROM Configs where NumOfPeople<={0} ORDER BY NumOfPeople", iNumOfPeople);
                            DataTable tbTmpLevels = SQLHelper.GetAll(sql);
                            if (tbTmpLevels != null && tbTmpLevels.Rows.Count > 0)
                            {
                                int iFromSecondRow = 0;
                                for (int j = 0; j < tbTmpLevels.Rows.Count; j++)
                                {
                                    int iNumOfPeopleFilted = int.Parse(tbTmpLevels.Rows[j]["NumOfPeople"].ToString());
                                    decimal dPercentPoint = decimal.Parse(tbTmpLevels.Rows[j]["percent"].ToString());
                                    for (int m = iFromSecondRow; m < iNumOfPeopleFilted; m++)
                                    {
                                        int memID = int.Parse(ListMemID[m].ToString());
                                        sqlQuery += string.Format(@"                        
                                        SET @FirstMoney = 
                                        (
                                            SELECT PointNumber FROM [Property] WHERE 
                                            MemberID={0}
                                        )", memID);
                                        sqlQuery += "\n";

                                        //Cộng phần trăm điểm cho các cấp
                                        sqlQuery += string.Format(@"
                                        update [Property]
                                        set pointNumber = (pointNumber+(cast({0} AS decimal(20,2))/100)*{1})
                                        WHERE MemberID={2}", dPercentPoint, point, memID);
                                        sqlQuery += "\n";

                                        sqlQuery += string.Format(@"                        
                                        SET @LastMoney = (SELECT PointNumber FROM [Property] WHERE MemberID={0})
                                        INSERT INTO TransactionLogs(MemberID,FirstMoney,LastMoney,ReasonChange)
		                                VALUES({0},@FirstMoney,@LastMoney,N'Ăn hoa hồng {3}% khi user ""{1}"" giao dich {2} points')"
                                            , memID, username, point, dPercentPoint);
                                        sqlQuery += "\n";

                                        //-----------------------------
                                        //Cộng phần trăm điểm userID giới thiệu Trực tiếp đc tuyến dưới
                                        string sqlPercentPoint = string.Format(@"
                                            DECLARE @percent decimal(20,2)
                                            DECLARE @value decimal(20,2)

                                            SET @percent = (select [percent] from [LevelConfigs]
                                            where [levelInfo]=(select [levelInfo] FROM Web_Members where memberid={0})
                                            and numofpeople=(SELECT count(*) FROM Web_Members WHERE UniqueCode like '{0}|%'))

                                            set @value= (cast(@percent AS decimal(20,2))/100)*{1}
                                            if(@value is not null)
	                                            select @value
                                            else
	                                            select 0", memID, point);
                                        decimal dPercent = decimal.Parse(SQLHelper.TExecuteScalar(sqlPercentPoint).ToString());
                                        decimal percentNumber = decimal.Parse(SQLHelper.TExecuteScalar(sqlPercentPoint.Replace("select @value", "select @percent")).ToString());

                                        if (dPercent > 0)
                                        {
                                            sqlQuery += string.Format(@"                        
                                            SET @FirstMoney = 
                                            (
                                                SELECT PointNumber FROM [Property] WHERE 
                                                MemberID={0}
                                            )", memID);
                                            sqlQuery += "\n";

                                            sqlQuery += string.Format(@"
                                            update [Property]
                                            set pointNumber = (pointNumber+{0})
                                            WHERE MemberID={1}", dPercent, memID);
                                            sqlQuery += "\n";

                                            sqlQuery += string.Format(@"                        
                                            SET @LastMoney = (SELECT PointNumber FROM [Property] WHERE MemberID={0})
                                            INSERT INTO TransactionLogs(MemberID,FirstMoney,LastMoney,ReasonChange)
		                                    VALUES({0},@FirstMoney,@LastMoney,N'Ăn hoa hồng Trực tiếp {3}% khi user ""{1}"" giao dich {2} points - Do có công giới thiệu tuyến dưới')"
                                                , memID, username, point, percentNumber);
                                            sqlQuery += "\n";
                                        }
                                        //-----------------------------

                                        if ((m + 1) == ListMemID.Count) break;
                                    }
                                    iFromSecondRow = iNumOfPeopleFilted;
                                }
                                sql = sqlTransBeginTry + "\n" + sqlQuery + "\n" + sqlTransEndTry + "\n" + sqlTransCatch;
                                bool isSuccess = bool.Parse(SQLHelper.TExecuteScalar(sql).ToString());
                                if (isSuccess)
                                {
                                    lblAlert.Text = "<font color='blue'><b>[Calculation success]</b></font>";
                                    lblAlert.Text += "<br><a href=\"TestingSystem.aspx\"><< Click here go back home page</a>";
                                }
                                else
                                {
                                    lblAlert.Text = "<font color='red'><b>[Error]</b></font>";
                                    lblAlert.Text += "<br><a href=\"TestingSystem.aspx\"><< Click here go back home page</a>";
                                }
                            }
                            break;
                        }
                    }
                }
            }
            else
            {
                if (!isValid)
                {
                    lblAlert.Text = "<font color='red'><b>[Not enough money]</b></font>";
                    lblAlert.Text += "<br><a href=\"TestingSystem.aspx\"><< Click here go back home page</a>";
                }
                else
                {
                    lblAlert.Text = "<font color='blue'><b>[Order success]</b></font>";
                    lblAlert.Text += "<br><a href=\"TestingSystem.aspx\"><< Click here go back home page</a>";
                }
            }
        }

    }
}
